Working with Hibernate Query Language (HQL)

Course: Advance Java Training

Topic: Working with Hibernate Query Language (HQL)

Course Link: Advance Java Training Course

Demo Link: Register for a Course Demo

JustAcademy Homepage: Visit JustAcademy

1. Introduction to Hibernate Query Language (HQL)

Hibernate Query Language (HQL) is an object-oriented query language provided by Hibernate ORM. It allows developers to retrieve, filter, update, and delete persistent data using entity classes and their Java properties instead of directly writing database-specific SQL statements.

HQL resembles SQL in its syntax, but the main difference is that HQL works with Java entities and their mapped attributes. Hibernate translates HQL statements into SQL appropriate for the configured database.

For example, suppose an application contains an entity named Employee with properties such as id, name, salary, and department. HQL can query this entity using its class or entity name and Java property names.

select e
from Employee e
where e.salary > 50000

This query retrieves employees whose salary is greater than 50,000. The query refers to the Java entity Employee and its salary property, rather than directly specifying a database table or column.

2. Learning Objectives

• Understand the purpose and benefits of HQL.
• Learn the differences between HQL and SQL.
• Write basic and advanced HQL select queries.
• Apply filtering, sorting, grouping, and aggregate functions.
• Use named and positional parameters safely.
• Perform joins between associated entities.
• Write subqueries and projections.
• Perform bulk update and delete operations.
• Understand pagination and query result handling.
• Identify common HQL errors and follow query-writing best practices.

3. Why Is HQL Used?

Applications frequently need to retrieve particular records instead of loading every record from a database. HQL provides an entity-oriented way to express these requirements.

Common uses include:

• Retrieving all employees or products.
• Finding employees by department or salary range.
• Searching for records using text patterns.
• Joining related entities such as employees and departments.
• Calculating totals, averages, minimums, and maximums.
• Grouping and summarizing business information.
• Updating or deleting multiple entities that satisfy a condition.
• Implementing search, reporting, and data-management features.

4. HQL vs. SQL

Example: SQL and HQL Comparison

SQL example:

SELECT name, salary
FROM employees
WHERE salary > 50000;

Equivalent HQL example:

select e.name, e.salary
from Employee e
where e.salary > 50000

The SQL query uses the employees table and its columns. The HQL query uses the Employee entity and its Java properties. The actual table and column mappings are determined by the entity configuration.

5. Preparing the Employee Entity

The following examples use an Employee entity. The entity must be mapped and registered with Hibernate before the queries can run.

import jakarta.persistence.*;

@Entity
@Table(name = "employees")
public class Employee {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(nullable = false)
    private String name;

    private double salary;

    private String department;

    public Employee() {
    }

    public Employee(String name, double salary, String department) {
        this.name = name;
        this.salary = salary;
        this.department = department;
    }

    public Long getId() {
        return id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public double getSalary() {
        return salary;
    }

    public void setSalary(double salary) {
        this.salary = salary;
    }

    public String getDepartment() {
        return department;
    }

    public void setDepartment(String department) {
        this.department = department;
    }
}

Note: This example uses the modern jakarta.persistence namespace. Older Hibernate projects may use javax.persistence. Use the namespace supported by your project's Hibernate and JPA dependencies.

6. Basic HQL Query Syntax

A typical HQL select query follows this structure:

select alias
from EntityName alias
where condition
order by alias.property

The main components are:

• select: Specifies which entities, properties, or expressions to return.
• from: Specifies the mapped entity being queried.
• where: Filters records according to a condition.
• order by: Sorts the results.
• group by: Groups results for aggregate calculations.
• having: Filters groups after aggregation.
• join: Combines data through mapped associations or explicit join conditions.

HQL can omit the select clause in many queries, but explicitly specifying the selection is usually easier to read.

7. Retrieving All Records

To retrieve all employees, use an HQL query that selects the Employee entity.

select e
from Employee e

Here, Employee is the entity name, and e is an alias used to reference the entity.

Executing the Query

List<Employee> employees = session.createQuery(
    "select e from Employee e",
    Employee.class
).getResultList();

for (Employee employee : employees) {
    System.out.println(
        employee.getName() + " - " + employee.getSalary()
    );
}

The typed createQuery() method declares the expected result type. The getResultList() method returns the matching entities as a Java list.

8. Using the WHERE Clause

The where clause restricts the records returned by an HQL query.

Example 1: Find Employees in a Department

select e
from Employee e
where e.department = 'IT'

Example 2: Find Employees Above a Salary

select e
from Employee e
where e.salary > 50000

Example 3: Multiple Conditions

select e
from Employee e
where e.department = 'IT'
and e.salary > 50000

Example 4: Using OR

select e
from Employee e
where e.department = 'IT'
or e.department = 'Finance'

Example 5: Excluding a Department

select e
from Employee e
where e.department <> 'HR'

Common comparison operators include =, <>, >, <, >=, and <=. Logical operators include AND, OR, and NOT.

9. Using Named Parameters

Named parameters allow values to be supplied separately from the HQL query. They improve readability and help prevent injection vulnerabilities caused by concatenating untrusted values into query strings.

String hql =
    "select e from Employee e where e.department = :dept";

List<Employee> employees = session.createQuery(
    hql,
    Employee.class
)
.setParameter("dept", "IT")
.getResultList();

In this example, :dept is a named parameter, and setParameter("dept", "IT") supplies its value.

Multiple Named Parameters

String hql =
    "select e from Employee e " +
    "where e.department = :dept " +
    "and e.salary >= :minSalary";

List<Employee> employees = session.createQuery(
    hql,
    Employee.class
)
.setParameter("dept", "IT")
.setParameter("minSalary", 50000.0)
.getResultList();

Named parameters are recommended for most application queries because their meaning is clear and values can be changed without rebuilding the query string.

10. Using Positional Parameters

HQL also supports positional parameter styles, but the exact syntax depends on the Hibernate version and API being used. In modern Hibernate HQL, numbered parameters such as ?1 can be used.

List<Employee> employees = session.createQuery(
    "select e from Employee e where e.department = ?1",
    Employee.class
)
.setParameter(1, "IT")
.getResultList();

Use the parameter syntax supported by your Hibernate version. Avoid concatenating user-provided values into HQL strings.

11. Sorting Results with ORDER BY

The order by clause sorts query results by one or more entity properties.

Ascending Order

select e
from Employee e
order by e.name asc

Descending Order

select e
from Employee e
order by e.salary desc

Sorting by Multiple Properties

select e
from Employee e
order by e.department asc, e.salary desc

ASC means ascending order, while DESC means descending order. If no direction is specified, ascending order is generally assumed.

12. Using LIKE for Pattern Matching

The LIKE operator searches text properties using patterns.

Example:

select e
from Employee e
where e.name like :pattern

List<Employee> employees = session.createQuery(
    "select e from Employee e where e.name like :pattern",
    Employee.class
)
.setParameter("pattern", "A%")
.getResultList();

Use % for any sequence of characters and _ for a single character. The behavior of case-sensitive matching depends on the database and collation.

13. Using IN, BETWEEN, IS NULL, and IS EMPTY

IN Operator

The IN operator matches a value against a list of possible values.

select e
from Employee e
where e.department in ('IT', 'Finance', 'HR')

A collection can also be passed as a parameter when supported by the query API and parameter type.

select e
from Employee e
where e.department in :departments

List<Employee> employees = session.createQuery(
    "select e from Employee e where e.department in :departments",
    Employee.class
)
.setParameter("departments", List.of("IT", "Finance"))
.getResultList();

BETWEEN Operator

select e
from Employee e
where e.salary between 30000 and 70000

BETWEEN includes both boundary values.

IS NULL Operator

select e
from Employee e
where e.department is null

This query finds employees whose department property has no value.

IS EMPTY Operator

For a collection-valued association, IS EMPTY checks whether the collection contains no elements.

select d
from Department d
where d.employees is empty

This example assumes that Department has a mapped collection property named employees.

14. Aggregate Functions in HQL

Aggregate functions calculate summary values from matching records.

Count Employees

select count(e)
from Employee e

Calculate Average Salary

select avg(e.salary)
from Employee e

Find the Highest Salary

select max(e.salary)
from Employee e

Calculate Total Salary

select sum(e.salary)
from Employee e

Aggregate queries commonly return a single scalar result when no grouping is used. Use the appropriate Java result type for the selected aggregate and the entity property type.

15. GROUP BY and HAVING

The GROUP BY clause groups records based on one or more properties. The HAVING clause filters those groups using aggregate conditions.

Count Employees by Department

select e.department, count(e)
from Employee e
group by e.department

Find Departments with More Than Five Employees

select e.department, count(e)
from Employee e
group by e.department
having count(e) > 5

Use WHERE to filter individual records before grouping and HAVING to filter groups after aggregation.

16. Joins in HQL

HQL supports joins for querying associated entities. Unlike a SQL join that typically names tables and columns, an HQL association join usually follows a mapped Java relationship.

Consider a Department entity with an employee collection and an Employee entity with a department reference.

Inner Join

select e
from Employee e
join e.department d
where d.name = :departmentName

This query retrieves employees associated with a department matching the supplied name. It assumes Employee.department is an entity association, not a string property.

Left Join

select e
from Employee e
left join e.department d

A left join includes employees even if their department association is missing, subject to the mapping and any additional filtering conditions.

Fetch Join

select d
from Department d
left join fetch d.employees
where d.id = :id

A fetch join loads the associated employees along with the department in the same query. This can reduce extra database queries when the collection is needed immediately. Use fetch joins thoughtfully, particularly when combining multiple collections or using pagination.

17. Selecting Specific Properties

HQL does not always need to return complete entities. It can select individual properties for reports and summaries.

select e.name, e.salary
from Employee e
where e.salary > 50000

A query that selects multiple scalar values commonly returns rows as object arrays when using an untyped result API, or as a suitable tuple or DTO when using a typed projection.

Using a DTO Projection

Suppose the application contains an EmployeeSummary class with a constructor that accepts a name and salary.

public class EmployeeSummary {
    private final String name;
    private final double salary;

    public EmployeeSummary(String name, double salary) {
        this.name = name;
        this.salary = salary;
    }

    public String getName() {
        return name;
    }

    public double getSalary() {
        return salary;
    }
}

The HQL constructor expression can create these objects directly:

select new com.example.dto.EmployeeSummary(e.name, e.salary)
from Employee e
where e.salary >= :minSalary

The class name must be fully qualified, and the constructor argument types must match the selected expressions. DTO projections are useful when the application needs only a subset of entity data.

18. Subqueries in HQL

A subquery is a query nested inside another query. It is useful when the outer query depends on a calculated value or result from another selection.

Employees Earning Above the Average Salary

select e
from Employee e
where e.salary > (
    select avg(e2.salary)
    from Employee e2
)

The inner query calculates the average salary. The outer query retrieves employees whose salaries exceed that value.

Using EXISTS

Suppose a department has a collection named employees. The following query retrieves departments that contain at least one employee with a salary above a threshold.

select d
from Department d
where exists (
    select e.id
    from Employee e
    where e.department = d
    and e.salary > :minSalary
)

Subqueries are useful for existence checks, comparisons with aggregate values, and complex filtering requirements.

19. Updating Records Using HQL

HQL supports bulk update statements. These operations update matching database records without loading every entity into memory individually.

update Employee e
set e.salary = :newSalary
where e.id = :employeeId

Executing a Bulk Update

Transaction transaction = session.beginTransaction();

int updatedCount = session.createMutationQuery(
    "update Employee e " +
    "set e.salary = :salary " +
    "where e.id = :id"
)
.setParameter("salary", 65000.0)
.setParameter("id", 101L)
.executeUpdate();

transaction.commit();

System.out.println("Affected entities: " + updatedCount);

This example uses Hibernate's mutation-query API available in modern Hibernate versions. In older versions, bulk updates are commonly executed with session.createQuery(...).executeUpdate().

Important: Bulk updates bypass normal per-entity dirty checking and may leave already-loaded entity objects in the persistence context with stale values. Flush pending changes before the operation when necessary, and clear or refresh affected managed entities afterward if the application needs their updated state.

20. Deleting Records Using HQL

HQL bulk delete statements remove all entities matching the specified condition.

delete from Employee e
where e.department = :departmentName

Example:

Transaction transaction = session.beginTransaction();

int deletedCount = session.createMutationQuery(
    "delete from Employee e " +
    "where e.department = :department"
)
.setParameter("department", "Temporary")
.executeUpdate();

transaction.commit();

System.out.println("Deleted entities: " + deletedCount);

Bulk deletes are powerful and should be used carefully. They do not automatically behave like removing each entity individually, so entity callbacks, cascades, and in-memory entity state may differ from ordinary entity removal. Verify foreign-key constraints and dependent records before executing a bulk delete.

21. INSERT Queries in HQL

Hibernate HQL supports insert statements in the form of insert into ... select ... in supported Hibernate versions. This is useful for copying data from one entity query into another entity mapping.

insert into ArchivedEmployee (name, salary, department)
select e.name, e.salary, e.department
from Employee e
where e.department = :department

This example assumes an entity named ArchivedEmployee with compatible properties. The selected values must match the target attributes and types. HQL insert-select is not the same as a general SQL insert with arbitrary literal values, and support and restrictions depend on Hibernate's query-language rules.

22. Pagination with HQL

Pagination returns a limited portion of the result set. It is useful for displaying large collections in web applications without retrieving every matching entity at once.

List<Employee> employees = session.createQuery(
    "select e from Employee e order by e.id",
    Employee.class
)
.setFirstResult(0)
.setMaxResults(10)
.getResultList();

• setFirstResult(0) sets the starting row offset.
• setMaxResults(10) limits the number of returned results.
• Ordering by a stable property helps make pagination predictable.

For subsequent pages, calculate the appropriate offset based on the page number and page size. Large offsets may be inefficient on some databases, so keyset pagination can be considered for large datasets.

23. Handling Query Results

Hibernate provides different methods for retrieving query results. The suitable method depends on whether the query should return a list, one result, or a single scalar value.

Use getSingleResult() when the query is expected to return exactly one result. For optional results, use an appropriate API supported by the Hibernate version or retrieve a list and check whether it is empty.

24. HQL Case Sensitivity

HQL keywords such as select, from, and where are generally case-insensitive. Java entity and property names must match the mapped names, including capitalization.

select e
from Employee e
where e.name = :employeeName

Use consistent capitalization for readability. If the entity property is named employeeName, referencing it as e.employeename may fail because property names are not treated like HQL keywords.

25. Common HQL Errors and Troubleshooting

26. HQL Best Practices

• Use entity names and Java property names rather than database table and column names.
• Prefer named parameters for readable, maintainable queries.
• Never concatenate untrusted user input into an HQL query.
• Select only the data needed for the operation.
• Use typed queries when the expected result is an entity or a specific result class.
• Use joins and fetch joins only when appropriate for the query's requirements.
• Use pagination for large result sets and apply stable ordering.
• Run update and delete operations within transactions.
• Review the effects of bulk mutations on the persistence context.
• Use aggregate functions for database-side calculations rather than loading every row into Java.
• Test queries against the actual entity mappings and supported Hibernate version.
• Enable SQL logging in development when diagnosing unexpected query behavior.
• Use native SQL only when a database-specific feature or requirement makes it necessary.

27. Practical Exercise: Employee Management Using HQL

Project Objective: Create an employee management application that uses HQL to retrieve, filter, sort, summarize, update, and delete employee records.

Requirements

1. Create and map the Employee entity.
2. Insert sample employees through Hibernate.
3. Retrieve all employees using a typed HQL query.
4. Find employees belonging to the IT department.
5. Find employees earning more than a specified salary.
6. Search employee names using the LIKE operator.
7. Sort employees by salary in descending order.
8. Count employees and calculate the average salary.
9. Group employees by department and count each group.
10. Use named parameters for all user-provided filter values.
11. Display only the first ten matching records.
12. Update an employee's salary through a bulk update query.
13. Delete test records using a carefully scoped delete query.
14. Verify database changes and handle transactions correctly.

Expected Outcome

By completing the exercise, learners should be able to construct HQL statements, execute queries through Hibernate, process results in Java, and understand the difference between entity-based operations and bulk mutation queries.

28. Interview Questions and Answers

Q1. What is HQL?

HQL stands for Hibernate Query Language. It is an object-oriented query language that works with mapped entities and their properties.

Q2. What is the main difference between HQL and SQL?

HQL refers to entities and Java properties, while SQL refers to database tables and columns. Hibernate translates HQL into SQL for execution.

Q3. What is an alias in HQL?

An alias is a short identifier assigned to an entity in a query. For example, e in from Employee e represents an employee entity reference.

Q4. What is the purpose of the WHERE clause?

The where clause filters records according to specified conditions.

Q5. Why are named parameters useful?

Named parameters separate values from query text, improve readability, and help prevent injection vulnerabilities when used correctly.

Q6. What is the difference between WHERE and HAVING?

WHERE filters individual records before grouping. HAVING filters groups, often using aggregate expressions.

Q7. Which aggregate functions are commonly used in HQL?

Common functions include COUNT(), SUM(), AVG(), MIN(), and MAX().

Q8. What is a fetch join?

A fetch join retrieves an associated entity or collection as part of the query's fetch plan. It can reduce additional database queries when related data is needed.

Q9. How do you perform a bulk update in HQL?

Use an update statement with the required assignments and conditions, then execute it using the mutation-query API or the API supported by the Hibernate version.

Q10. How do you perform a bulk delete in HQL?

Use a delete from statement with an appropriate condition. Execute it within a transaction and verify that the operation will not violate database constraints.

Q11. What is a subquery?

A subquery is a query nested within another query. It can be used for comparisons, aggregate calculations, and existence checks.

Q12. What does setMaxResults() do?

setMaxResults() limits the number of query results returned. It is commonly used with setFirstResult() to implement pagination.

Q13. What is a bulk mutation query?

A bulk mutation query updates or deletes multiple matching entities directly through a query rather than loading and modifying each entity individually.

Q14. Why can bulk updates cause stale entity state?

Bulk updates do not automatically synchronize already-loaded entity objects in the persistence context. The application may need to clear or refresh those objects.

Q15. When should native SQL be used instead of HQL?

Native SQL may be appropriate when a database-specific feature, specialized query, or SQL operation cannot be expressed effectively with the HQL capabilities available in the project's Hibernate version.

29. Quick Revision Checklist

• HQL queries mapped entities and Java properties.
• SELECT retrieves entities, properties, or expressions.
• WHERE filters matching records.
• ORDER BY sorts query results.
• GROUP BY groups results for aggregation.
• HAVING filters grouped results.
• Named parameters use syntax such as :department.
• Joins can navigate mapped entity associations.
• Aggregate functions summarize data.
• Subqueries allow nested query logic.
• Bulk updates and deletes should be executed carefully within transactions.
• Pagination uses methods such as setFirstResult() and setMaxResults().
• HQL keywords are generally case-insensitive, but entity and property names must be correct.
• Hibernate translates HQL into SQL for the configured database.

30. Conclusion

Hibernate Query Language is an important part of Hibernate ORM because it allows developers to work with persistent data through entity-oriented queries. By learning HQL syntax, named parameters, filtering, sorting, aggregation, joins, subqueries, pagination, and bulk mutations, developers can implement flexible database operations in Java applications.

Practice each query against a working Hibernate project, verify the generated SQL when troubleshooting, and always consider transactions, entity mappings, and persistence-context behavior when updating or deleting data.

31. Additional Learning Resources

• Explore the Advance Java Training Course at JustAcademy
• Register for a JustAcademy Course Demo
• Visit the JustAcademy Homepage
• Read the Official Hibernate Query Language Guide